﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.Win32;

namespace m_pricelist._DAL_
{   

   public class ProductsData
   {
       CreateConnection createConn = new CreateConnection();

       public DataSet getIndustry()
       {
           DataSet ds;
           string sql;
           sql = "SELECT distinct(INDUSTRY) FROM PROVEN_SOLUTION";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getSubIndustry(string selectedIndustry)
       {
           DataSet ds;
           string sql;
           sql = "SELECT distinct(SUB_INDUSTRY) FROM PROVEN_SOLUTION WHERE INDUSTRY = '" + selectedIndustry + "'";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       /// <summary>
       /// Gets the product units
       /// </summary>
       /// <returns></returns>
       public DataSet getProductUnits()
       {
           DataSet ds;
           string sql;
           sql = "SELECT distinct(PRODUCT_GROUP) FROM PRICELIST";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       /// <summary>
       /// Gets the product types
       /// </summary>
       /// <param name="strProUnit"></param>
       /// <returns></returns>
       public DataSet getProductTypes(string strProUnit)
       {
           DataSet ds;
           string sql;
           sql = "SELECT distinct(TYPE_NAME) FROM PRICELIST WHERE PRODUCT_GROUP = '" + strProUnit + "'";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       /// <summary>
       /// Gets the products
       /// </summary>
       /// <param name="strProType"></param>
       /// <returns></returns>
       public DataSet getProducts(string strProType)
       {
           DataSet ds;
           string sql;
           sql = "SELECT distinct(PRODUCT_NAME) FROM PRICELIST WHERE TYPE_NAME = '" + strProType + "'";          
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getProduct(string strItemNumber)
       {
           DataSet ds;
           string sql;
           sql = "SELECT distinct(PRODUCT_NAME) FROM PRICELIST WHERE ITEM_NUMBER = '" + strItemNumber + "'";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       /// <summary>
       /// Gets item number, item name price and comment 
       /// </summary>
       /// <param name="strProType"></param>
       /// <param name="strProduct"></param>
       /// <returns></returns>
       public DataSet getSprItemsData(string strProType,string strProduct)
       {
           DataSet ds;
           string sql = "";
           if (strProduct == "")
           {
               sql = "SELECT ITEM_NUMBER, ITEM_NAME, PRICE,'' AS UNITS, COMMENT FROM PRICELIST WHERE TYPE_NAME = '" + strProType + "'";
           }
           else
           {
                sql = "SELECT ITEM_NUMBER, ITEM_NAME, PRICE,'' AS UNITS, COMMENT FROM PRICELIST WHERE TYPE_NAME = '" + strProType + "' AND PRODUCT_NAME = '" + strProduct + "' order by ITEM_NAME";
           }
           
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getSprItemsData(string strProType)
       {
           DataSet ds;
           string sql = "";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getOptAndConfGroups(string strGroupId)
       {
           DataSet ds;
           string sql = "SELECT distinct(OPTIONGROUP) FROM PL_OPTIONS WHERE GROUP_ID = " + strGroupId + " order by OPTIONGROUP";
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public string getGroupId(string strItemNumber)
       {
           DataSet ds;
           string sql = "SELECT OPTIONS_GROUP FROM PRICELIST WHERE ITEM_NUMBER ='" + strItemNumber + "' order by ITEM_NAME";
           string tempstr;
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           tempstr = ds.Tables[0].Rows[0][0].ToString();

           if (tempstr == "")
           {
               return "0";
           }
           return tempstr;
       }

       public DataSet getOptAndConfItems(string strOptionGroup,string strGroupId)
       {
           DataSet ds;
           string sql = "SELECT ITEM_NUMBER, ITEM_NAME, PRICE,'' AS UNITS, COMMENT FROM PL_OPTIONS WHERE OPTIONGROUP = '" + strOptionGroup + "' AND GROUP_ID = " + strGroupId;
           ds = new DataSet();
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getSprQuoteItems(string strItemNumber)
       {
           DataSet ds;
           string sql = "SELECT  ITEM_NUMBER, ITEM_NAME, PRICE,'' AS UNITS, COMMENT, TRANSFERPRICE, SUPPLIER_NAME FROM PRICELIST WHERE ITEM_NUMBER = '" + strItemNumber + "' order by ITEM_NAME";
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getSprQuoteOptions(string strOptionNumber)
       {
           DataSet ds;
           string sql = "SELECT  ITEM_NUMBER, ITEM_NAME, PRICE,'' AS UNITS, COMMENT, TRANSFERPRICE, SUPPLIER_NAME FROM PL_OPTIONS WHERE ITEM_NUMBER = '" + strOptionNumber + "' order by ITEM_NAME";
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public string getRegPath()
       {
           string regPath;
           regPath = createConn.getRegPath();
           return regPath;
       }

       public string getRegPath(string db)
       {
           string regPath;
           regPath = createConn.getRegPath(db);
           return regPath;
       }

       public DataSet getExcelDataItems(string strItemNumber)
       {
           DataSet ds;
           string sql = "SELECT '' AS SLA, '' AS ID, A.ITEM_NUMBER, A.ITEM_NAME, '' AS PRICE, '' AS UNITS, '' AS TOTAL, '' AS DISCOUNT, '' AS SUB, '' AS COM, '' AS COMMISSION, A.TRANSFERPRICE, A.SUPPLIER_NAME,  B.SERVICE_VALUE,  B.REPAIR_TIME, B.INSPECTION_TIME, B.HB_TIME, B.INSTALLATION_DAYS, B.TRAINING_DAYS, '' AS S_INSTALLATION_DAYS, '' AS S_TRAINING_DAYS, A.COMMENT, A.ITEM_TYPE, A.LISTPRICE, A.CFI, '' AS ADJUST, '' AS ADJUST_CUR,'' AS EQUIPMENT, '' AS SORT1, A.PCODE  FROM pricelist AS A LEFT JOIN SERVICE_VIEW AS B ON A.ITEM_NUMBER = B.ITEM_NUMBER WHERE A.ITEM_NUMBER = '" + strItemNumber + "'";    
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getExcelDataOptions(string strItemNumber)
       {
           DataSet ds;
           string sql = "SELECT '' AS SLA, '' AS ID, A.ITEM_NUMBER, A.ITEM_NAME, '' AS PRICE, '' AS UNITS, '' AS TOTAL, '' AS DISCOUNT, '' AS SUB, '' AS COM, '' AS COMMISSION, A.TRANSFERPRICE, A.SUPPLIER_NAME,  B.SERVICE_VALUE,  B.REPAIR_TIME, B.INSPECTION_TIME, B.HB_TIME, B.INSTALLATION_DAYS, B.TRAINING_DAYS, '' AS S_INSTALLATION_DAYS, '' AS S_TRAINING_DAYS, A.COMMENT, A.ITEM_TYPE, A.LISTPRICE, A.CFI, '' AS ADJUST, '' AS ADJUST_CUR,'' AS EQUIPMENT, '' AS SORT1, A.PCODE  FROM PL_OPTIONS AS A LEFT JOIN SERVICE_VIEW AS B ON A.ITEM_NUMBER = B.ITEM_NUMBER WHERE A.ITEM_NUMBER = '" + strItemNumber + "'";
           ds = createConn.ExecuteSql(sql);
           return ds;
       }

       public DataSet getServiceValueData(string strItemNumber)
       {
           DataSet ds;
           string sql = "SELECT SERVICE_VALUE, REPAIR_TIME, INSPECTION_TIME, HB_TIME, INSTALLATION_DAYS, TRAINING_DAYS FROM SERVICE_VIEW WHERE ITEM_NUMBER = '" + strItemNumber + "'";
           ds = createConn.ExecuteSql(sql);
           return ds;
       }
       //TODO öll comment á ensku
       //Fall nær í gögn úr Appendix View töflunni úr gagnagrunni. Skilar DataSet. Er notað í getB_Appendix á BAL layer og þar er kallað í það í Proposal forminu
       public DataSet getD_Appendix()
       {
           DataSet ds;
           string sql = "Select ID, Short_Name, AppName, Supplier_Name from Appendix_View order by Supplier_Name";
           ds = createConn.ExecuteSql(sql);
           return ds;
       }
       //TODO getParmUpdate(string parameter) klára þetta
       public DataSet getParmUpdate(string parameter)
       {
           DataSet ds;
           string sql = "";

                 if (parameter == "CrossRate")
                {
                     sql = "SELECT  ID, SUPPLIER_NAME, CROSSRATE, CURRENCYD FROM SUPPLIER ORDER BY SUPPLIER_NAME";           
                }
                
                if (parameter == "CFI")
                {
                    sql = "SELECT  C.ID,S.SUPPLIER_NAME, C.DESCRIPTION, FORMAT(C.CFI,'0%') AS CFI FROM COMMISSION C, SUPPLIER S where S.ID = C.SUPPLIER_ID ORDER BY S.SUPPLIER_NAME";
                }
                
                if (parameter == "Commission")
                {
                    sql = "SELECT C.ID, S.SUPPLIER_NAME, C.DESCRIPTION, format(C.COM_PERCENT,'0%') as COMMISSION  FROM COMMISSION C, SUPPLIER S WHERE S.ID = C.SUPPLIER_ID ORDER BY S.SUPPLIER_NAME";
                }
                
           ds = createConn.ExecuteSql(sql);
           DataColumn Col = ds.Tables[0].Columns.Add("Choose", System.Type.GetType("System.Boolean"));
           Col.SetOrdinal(0);// to put the column in position 0;
           return ds;
       }

       public void setDb(string newDb)
       {
           createConn.regPath = newDb;
       }

       public void updateDB_basic(string updateParameter,string tempstring)
       {
           if (updateParameter == "Currency")
           {
               string sql = "UPDATE DB_basic SET Pmvalue = '" + tempstring + "' WHERE PmName = 'Currency'";
               //createConn.ExecuteSql(sql);
           }

           if (updateParameter == "Language")
           {
               string sql = "";
               createConn.ExecuteSql(sql);
           }

           if (updateParameter == "solutionType")
           {
               string sql = "";
               createConn.ExecuteSql(sql);
           }

           if (updateParameter == "Reseller")
           {    
               string sql = "";
               createConn.ExecuteSql(sql);
           }
       }
        
   }
}
